﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using MySql.Data.MySqlClient;

namespace Plazos
{
    class CursoBD: DBConnect
    {

        protected int sic;
        protected int idSem;
        protected int horas;
        protected int dias;
        protected int horasxdia;
        protected string fechaInicioLect;
        protected string fechaTermLect;
        protected string modificar;
        protected string colocar;
        protected string fechaInicioPractica;
        protected string fechaPractica;
        protected string insOcular;
        protected string relatores;
        protected string supervision50;
        protected string encuesta75;
        protected string publicar;
        protected string correrLista;
        protected string visitaInfraestructura;
        protected string acuerdoOperativo;
        protected string f1;
        protected string f1C;
        protected string ingresoSence;
        protected string f2;
        protected string f2C;
        protected string f3;
        protected string seguimiento;
        protected string ingSeguimiento1;
        protected string ingSeguimiento2;
        protected string f4;
        protected string certificado; // ceremonia
        protected string entreCertif; // entrega del certificado si es que no asisten a la ceremonia
        private string query;
        private MySqlCommand cmd;
        protected String error = "0";
        
        public bool Insertar(int  idSem)
        {
            //revisar que no existe..
            query = "SELECT COUNT(*) FROM curso WHERE sic = '" + sic + "';";
            if (this.OpenConnection() == true)
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    int cant = Convert.ToInt32(cmd.ExecuteScalar());
                    if (cant > 0)
                    {
                        error = "El curso que intenta ingresar ya existe en el sistema";
                        CloseConnection();
                        return false;
                    }
                    else
                    {
                        //insertar
                        query = "INSERT INTO curso (sic, idSem, horas, dias, horasxdia, fechaInicioLect, fechaTermLect, modificar, colocar, fechaInicioPractica, fechaPractica, insOcular, relatores, supervision50, encuesta75, publicar, correrLista, visitarInfraestructura, acuerdoOperativo, f1, f1C, ingresoSence, f2, f2C, f3, seguimiento, ingSeguimiento1, ingSeguimiento2, f4, certificado, entreCertif )"
                            +"VALUES('" + sic + "','" 
                            + idSem + "','"
                            + horas + "','"
                            + dias + "','"
                            + horasxdia + "','"
                            + fechaInicioLect + "','"
                            + fechaTermLect + "','"
                            + modificar + "','"
                            + colocar + "','" 
                            + fechaInicioPractica + "','"
                            + fechaPractica + "','" 
                            + insOcular + "','" 
                            + relatores + "','" 
                            + supervision50 + "','" 
                            + encuesta75 + "','" 
                            + publicar + "','" 
                            + correrLista + "','" 
                            + visitaInfraestructura + "','" 
                            + acuerdoOperativo + "','" 
                            + f1 + "','" + f1C + "','" 
                            + ingresoSence + "','" 
                            + f2 + "','" 
                            + f2C + "','" 
                            + f3 + "','" 
                            + seguimiento + "','" 
                            + ingSeguimiento1 + "','" 
                            + ingSeguimiento2 + "','" 
                            + f4 + "','" 
                            + certificado + "','" 
                            + entreCertif + "')";
                        Console.WriteLine(query);
                        //create command and assign the query and connection from the constructor
                        cmd = new MySqlCommand(query, connection);

                        //Execute command
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message);
                            error = e.Message;
                            this.CloseConnection();
                            return false;
                        }

                        //close connection
                        this.CloseConnection();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    error = e.Message;
                    this.CloseConnection();
                    return false;
                }
                return true;
            }
            else
            {
                return false;
            }

        }

        
       /* public Curso getCurso(string sic)
        {

            Curso c = new Curso();

            query = "SELECT FROM curso where sic = '" + sic + "';";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return c;
                }
                finally
                {
                    CloseConnection();
                }

                return c;

            }
        }*/

        public List<Curso> getCursos()
        {
            List<Curso> cursos = new List<Curso>();
            query = "SELECT * FROM curso";
            if (OpenConnection())
            {
                Console.WriteLine("entra aca al open ");
                cmd = new MySqlCommand(query, connection);
                try
                {
                    MySqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        cursos.Add(new Curso(dr.GetInt32(0), dr.GetInt32(1), dr.GetInt32(2), dr.GetInt32(3), dr.GetInt32(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetString(24), dr.GetString(25), dr.GetString(26), dr.GetString(27), dr.GetString(28), dr.GetString(29), dr.GetString(30)));
                        
                    }
                    dr.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                }
                finally
                {
                    CloseConnection();
                }
            }
            return cursos;

        }

        public bool eliminarCurso(string a)
        {
            sic = Convert.ToInt32(a);
            query = "DELETE FROM curso where sic = '" + sic + "';";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }

        public bool eliminarCursoPorSemestre (int idSem)
        {
            //sic = Convert.ToInt32(a);
            query = "DELETE FROM curso where idSem = '" + idSem + "';";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }

        public bool modificarPublicar(string fecha, int id) {

            query = " UPDATE curso SET publicar = '" + fecha + "' WHERE idSem = " + id + ";";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }

        public bool modificarCurso( int sic)
        {

            query = " UPDATE curso SET horas = " + horas + ", horasxdia = " + horasxdia + ", dias = " + dias + ", fechaInicioLect = '" + fechaInicioLect + "', fechaTermLect = '" + fechaTermLect + "', modificar = '" + modificar + "', colocar = '" + colocar + "', fechaInicioPractica = '" + fechaInicioPractica + "' , fechaPractica = '" + fechaPractica + "', relatores = '" + relatores + "', supervision50 = '" + supervision50 + "',encuesta75 = '" + encuesta75 + "' , correrLista = '" + correrLista + "', acuerdoOperativo = '" + acuerdoOperativo + "', f1 = '" + f1 + "' , f1C = '" + f1C + "', ingresoSence = '" + ingresoSence + "', f2 = '" + f2 + "', f2C = '" + f2C + "',f3 = '" + f3 + "', seguimiento = '" + seguimiento + "', ingSeguimiento1 = '" + ingSeguimiento1 + "', ingSeguimiento2 = '" + ingSeguimiento2 + "',f4 = '" + f4 + "', certificado = '" + certificado + "',entreCertif = '" + entreCertif + "' WHERE  sic = " + sic + ";";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }

    
    }
}
